/*******************************************************************************
 * Copyright (c) 2011 Jovansonlee B. Cesar.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Public License v2.0
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 * 
 * Contributors:
 *     Jovansonlee B. Cesar - initial API and implementation
 ******************************************************************************/
package com.ivanceras.shared.db;

import com.ivanceras.shared.exception.DataTypeException;
import com.ivanceras.shared.util.Configuration;
import com.ivanceras.shared.util.SpecialCase;

public abstract class DB_Rdbms {


	protected String buildCreateTableStatement(ModelDef model){
		String schema = Configuration.dbSchema;
		schema = getDBElementName(model,schema);

		String table = getDBTableName(model);

		String[] columns = model.getAttributes();
		for(int i = 0; i < columns.length; i++){
			columns[i] = getDBElementName(model,columns[i]);
		}

		String[] dataTypes = model.getDataTypes();

		String autoIncrementColumn = model.getGeneratedAttribute();
		autoIncrementColumn = getDBElementName(model,autoIncrementColumn);

		String[] primaryColumns = model.getPrimaryAttributes();
		for(int i = 0; i < primaryColumns.length; i++){
			primaryColumns[i] = getDBElementName(model,primaryColumns[i]);
		}
		String[] uniqueColumns = model.getUniqueAttributes();
		for(int i = 0; i < uniqueColumns.length; i++){
			uniqueColumns[i] = getDBElementName(model,uniqueColumns[i]);
		}
		String[] hasOne = model.getHasOne();
		if(hasOne != null){
			for(int i = 0; i < hasOne.length; i++){
				hasOne[i] = getDBElementName(model,hasOne[i]);
			}
		}

		StringBuffer sql = new StringBuffer();
		sql.append("CREATE TABLE ");
		if(schema != null){
			sql.append(schema+".");
		}
		sql.append(table);
		sql.append("(");
		boolean doComma = false;
		for(int i = 0; i < columns.length; i++){
			if(doComma){
				sql.append(", ");
			}else doComma = true;
			sql.append(columns[i]);
			if(autoIncrementColumn.equals(columns[i])){
				sql.append(" "+getAutoIncrementColumnConstraint());
			}else{
				String dbDataType = null;
				try {
					dbDataType = getEquivalentDBDataType(dataTypes[i]);
				} catch (DataTypeException e) {
					e.printStackTrace();
				}
				if(dbDataType!=null){
					sql.append(" "+dbDataType);
				}
			}
		}
		if(primaryColumns!= null && primaryColumns.length > 0){
			sql.append(", CONSTRAINT "+table+"_pkey PRIMARY KEY (");
			boolean doComma2 = false;
			for(int i = 0; i < primaryColumns.length; i++){
				if(doComma2)sql.append(", ");
				else doComma2 = true;
				sql.append(primaryColumns[i]);
			}
			sql.append(")");
		}

		if(uniqueColumns != null && uniqueColumns.length > 0){
			sql.append(", CONSTRAINT "+table+"_uindex UNIQUE (" );
			boolean doComma3 = false;
			for(int i = 0; i < uniqueColumns.length; i++){
				if(doComma3) sql.append(", ");
				else doComma3 = true;
				sql.append(uniqueColumns[i]);
			}
			sql.append(")");
		}

		if(hasOne != null && hasOne.length > 0){
			for(int i = 0; i < hasOne.length; i++){
				sql.append(", CONSTRAINT "+table+"_"+hasOne[i]+"_fkey FOREIGN KEY ("+model.getForeignKeyColumn(hasOne[i])+")");
				sql.append(" REFERENCES "+hasOne[i]+"("+model.getPrimaryColumn()+")");
				sql.append(" MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ");
			}
		}
		sql.append(")");
		return sql.toString();
	}

	/**
	 * Different implementation accross database
	 * @return
	 */
	protected abstract String getAutoIncrementColumnConstraint();


	protected static Object[] getFilterValues(Filter[] filters){
		if(filters != null){
			Object[] filterValues = new Object[filters.length];
			for(int i = 0; i < filters.length; i++){
				if(filters[i] != null){
					filterValues[i] = filters[i].value;
				}
			}
			return filterValues;
		}
		return null;
	}

	protected static String buildDeleteStatement(ModelDef model, Filter[] filters){
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		//String schema = null;
		String table = getDBTableName(model);



		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM ");
		if(schema != null){
			sql.append(schema+".");
		}
		sql.append(table);
		if(filters != null && filters.length > 0){
			sql.append(" WHERE ");
			boolean doAnd = false;
			for(int i = 0; i < filters.length; i++){
				if(filters[i] != null){
					if(doAnd) sql.append(" AND ");
					else doAnd = true;
					sql.append(getDBElementName(model, filters[i].attribute));
					sql.append(" "+getEquivOp(filters[i].operator)+" ? ");
				}
			}
		}
		return sql.toString();
	}

	protected static String buildDropStatement(ModelDef model) {
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		String table = getDBTableName(model);

		StringBuffer sql = new StringBuffer();
		sql.append("DROP TABLE IF EXISTS ");
		if(schema != null){
			sql.append(schema+".");
		}
		sql.append(table);
		return sql.toString();
	}


	protected static String buildEmptyTableStatement(ModelDef model) {
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		//String schema = null;
		String table = getDBTableName(model);

		StringBuffer sql = new StringBuffer();
		sql.append("TRUNCATE TABLE ");
		if(schema != null){
			sql.append(schema+".");
		}
		sql.append(table);
		sql.append(" CASCADE");
		return sql.toString();
	}

	protected static String buildInsertStatement(ModelDef model) {
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		//String schema = null;
		String table = getDBTableName(model);
		String[] columns = model.getAttributes();
		for(int i = 0; i < columns.length; i++){
			columns[i] = getDBElementName(model,columns[i]);
		}
		String autoIncrementColumn = model.getGeneratedAttribute();
		//		System.out.println("auto increment column: "+autoIncrementColumn);
		autoIncrementColumn = getDBElementName(model,autoIncrementColumn);

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		if(schema != null){
			sql.append(schema+".");
		}
		sql.append(table);
		if(columns != null){
			sql.append(" (");
			boolean doCommaCol = false;
			for(int c = 0; c < columns.length; c++){
				//System.out.println(autoIncrementColumn+" ?==? "+columns[c]);
				if(autoIncrementColumn != null && autoIncrementColumn.equals(columns[c])){//this is the auto increment column
					;
				}
				else{
					if(doCommaCol)sql.append(", ");else doCommaCol = true;
					sql.append(columns[c]);
				}
			}
			sql.append(")");
		}
		sql.append(" VALUES(");
		boolean doComma = false;
		for(int i = 0; i < columns.length; i++){
			if(autoIncrementColumn != null && autoIncrementColumn.equals(columns[i])){
				;
			}else{
				if(doComma)	sql.append(", "); else doComma = true;
				sql.append("?");

			}
		}
		sql.append(")");
		if(autoIncrementColumn != null){
			sql.append(" RETURNING "+autoIncrementColumn);
		}
		return sql.toString();	
	}


	protected static String buildSelectStatement(ModelDef model, Join join, Filter[] filters, Integer offset, Integer limit, Boolean doCount, Order[] orders) {
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		//String schema = null;
		String table = getDBTableName(model);
		String[] columns = model.getAttributes();
		for(int i = 0; i < columns.length; i++){
			columns[i] = getDBElementName(model,columns[i]);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		if(doCount != null && doCount){
			sql.append(" count(*)");
		}else{
			boolean doComma = false;
			for(int i = 0; i < columns.length; i++){
				if(doComma) sql.append(", "); else doComma = true;
				if(join != null){
					sql.append(" "+table+".");
				}
				sql.append(columns[i]);
			}
		}
		sql.append(" FROM ");
		if(join == null){
			if(schema != null){
				sql.append(schema+".");
			}
			sql.append(table);
		}
		if(join != null){
			sql.append(" "+join.toString());
		}
		boolean doAnd = false;
		if(filters != null && filters.length > 0){
			sql.append(" WHERE ");
			for(int i = 0; i < filters.length; i++){
				if(filters[i] != null ){
					if(doAnd) sql.append(" AND ");
					else doAnd = true;
					sql.append(getDBElementName(model, filters[i].attribute));
					sql.append(" "+getEquivOp(filters[i].operator)+" ? ");
				}
			}
		}
		if(orders != null && orders.length > 0){
			boolean doOrderComma = false;
			sql.append(" ORDER BY ");
			for(int i = 0; i < orders.length; i++){
				if(orders[i] != null){
					if(doOrderComma){
						sql.append(",");
					}else{doOrderComma = true;}
					sql.append(" "+orders[i].getColumn());
					sql.append(" "+getEquivSort(orders[i].isAscending()));
				}
			}
		}
		if(doCount != null && doCount){
			;
		}else{
			if(limit != null){
				sql.append(" LIMIT "+limit);
				if(offset != null ){
					sql.append(" OFFSET "+offset);
				}
			}
		}
		return sql.toString();
	}

	protected static String buildUpdateStatement(ModelDef model, Filter[] filters) {
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);
		String table = getDBTableName(model);
		String[] columns = model.getAttributes();

		for(int i = 0; i < columns.length; i++){
			columns[i] = getDBElementName(model,columns[i]);
		}
		String autoIncrementColumn = model.getGeneratedAttribute();
		autoIncrementColumn = getDBElementName(model, autoIncrementColumn);

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		if(schema != null){
			sql.append(schema+".");
		}
		boolean doComma = false;
		sql.append(table);
		boolean appendSet = true;
		for(int i = 0; i < columns.length; i++){
			if(appendSet){sql.append(" SET ");appendSet = false; }
			if(autoIncrementColumn != null && columns[i].equals(autoIncrementColumn)){
				;
			}else{
				if(doComma) sql.append(", "); else doComma = true;
				sql.append(columns[i]);
				sql.append(" = ? ");
			}
		}
		sql.append(" WHERE ");
		boolean doAnd = false;
		for(int i = 0; i < filters.length; i++){
			if(doAnd) sql.append(" AND "); else doAnd = true;
			sql.append(getDBElementName(model, filters[i].attribute));
			sql.append(" "+getEquivOp(filters[i].operator)+" ? ");
		}
		return sql.toString();
	}

	protected static String getEquivOp(String operator){
		if(operator.equals(Filter.EQUAL)) return "=";
		if(operator.equals(Filter.GREATER_THAN)) return ">";
		if(operator.equals(Filter.GREATER_THAN_OR_EQUAL)) return ">=";
		if(operator.equals(Filter.IN)) return "IN";
		if(operator.equals(Filter.LESS_THAN)) return "<";
		if(operator.equals(Filter.LESS_THAN_OR_EQUAL)) return "<=";
		if(operator.equals(Filter.NOT_EQUAL)) return "!=";
		if(operator.equals(Filter.LIKE)) return "LIKE";
		return "=";
	}

	protected static String getEquivSort(boolean isAscending){
		if(isAscending){
			return "ASC";
		}else{
			return "DESC";
		}
	}


	/**
	 * returns the proper table naming considering case sensitivity
	 * @param model
	 * @return
	 */
	protected static String getDBTableName(ModelDef model){
		String element = getDBElementName(model, model.getModelName());
		return element;
	}
	/**
	 * applies to tableSchema and elementName
	 * @param model
	 * @return
	 */
	public static String getDBElementName(ModelDef model, String elementName){
		if(model.isCaseSensitive()){
			return "\""+elementName+"\"";
		}else{
			return SpecialCase.correctKeyWords(elementName);
		}
	}



	protected boolean isCaseSensitive(String tableName, String realTableName){
		int cmp = realTableName.compareTo(tableName.toLowerCase());
		return !(cmp == 0);
	}

	
	protected abstract String getEquivalentJavaDataType(String dbDataType) throws DataTypeException;
	
	protected abstract String getEquivalentDBDataType(String javaDataType) throws DataTypeException;
	
	protected abstract Object convertDaoValueToDB(DAO dao, String column);
}
